# This Python file uses the following encoding: utf-8
import sys, os, psycopg2, configparser
import pandas as pd
import openpyxl
from pathlib import Path

from PySide6.QtWidgets import QApplication, QMainWindow, QWidget, QFileDialog, QVBoxLayout, QTableWidget, QTableWidgetItem, QAbstractItemView, QPushButton
from PySide6.QtCore import QUrl, Qt
from PySide6.QtGui import QDesktopServices

from mainform_ui import Ui_MainWindow
from actionaboutform_ui import Ui_actionaboutform 
from DB_initialform_ui import Ui_DBinitialForm
from loadfileform_ui import Ui_openfileForm
from tabwidgetshow import tabWidgetDrawingShow, tabWidgetTableShow

from PrincipalComponent import PCAWidget
from DataReader import XMLDataReader, ExcelDataReader

#import xml.etree.ElementTree as ET


class OpenFileForm(QWidget, Ui_openfileForm):
    def __init__(self, parent=None):
        super(OpenFileForm,self).__init__()
        self.setupUi(self)

        self.file_path = None
        
        self.material_comboBox.currentTextChanged.connect(self.write_rencenttempstore)
        self.shape_comboBox.currentTextChanged.connect(self.write_rencenttempstore)
        self.dateEdit.dateChanged.connect(self.write_rencenttempstore)
        self.lineEdit.textChanged.connect(self.write_rencenttempstore)
        self.openfile_Button.clicked.connect(self.on_openfile_Button_clicked)

    def on_openfile_Button_clicked(self):
        file_dialog = QFileDialog()
        self.file_path, _ = file_dialog.getOpenFileName(self, "Select Data File", "","XML Files (*.xml);;Excel Files (*.xlsx *.xls)")
        if self.file_path:
            #self.openfile_label.setText(self.file_path)
            self.write_rencenttempstore()  
            mainwidget.tabWidget_Show(self.file_path) # mainwidget是主程序的全局变量，可直接调用

    def write_rencenttempstore(self):
        config = configparser.ConfigParser()
        with open('.\datauser\currentstore.ini', encoding='utf-8') as config_file:
            config.read(config_file) 
        config['Current File Store Info'] = {}
        config['Current File Store Info']['date'] = self.dateEdit.date().toString(Qt.ISODate)
        config['Current File Store Info']['location'] = self.lineEdit.text() #.encode('utf-8').decode('gbk')
        config['Current File Store Info']['material'] = self.material_comboBox.lineEdit().text()   
        config['Current File Store Info']['shape'] = self.shape_comboBox.lineEdit().text() 
        config['Current File Store Info']['filepath'] = str(self.file_path) #self.openfile_label.text()
       
        config_file_path = os.path.join(os.getcwd(), '.\datauser\currentstore.ini')
        with open(config_file_path, 'w', encoding='utf-8') as config_file:
            config.write(config_file)     

class AboutForm(QWidget, Ui_actionaboutform):
    def __init__(self, parent=None):
        super(AboutForm,self).__init__()
        self.setupUi(self)        

class DBinitialForm(QWidget, Ui_DBinitialForm):
    def __init__(self, parent=None):
        super(DBinitialForm,self).__init__()
        self.setupUi(self)

class DBOperate():
    def __init__(self, db_port, db_username, db_password):
        self.port = db_port
        self.username = db_username
        self.password = db_password
        self.conn = None
                 
    def connect(self):
        try:    
            self.conn = psycopg2.connect(
                host="localhost",
                port=self.port,
                database="postgres",#也可用dbname="postgres"？
                user=self.username,
                password=self.password,
            )
            return "OK"
        except:
            return "Fail"

    def sampleinitial(self):
        cursor = self.conn.cursor()
        self.conn.autocommit = True
        cursor.execute('''
            CREATE SCHEMA IF NOT EXISTS wolseemmt
                AUTHORIZATION postgres;
        ''') 
        cursor.execute("SELECT EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema = 'wolseemmt' AND table_name = 'samplemmt');")
        sql_resoult = cursor.fetchone()
        #print(sql_resoult[0])
        if not sql_resoult[0]: # If the "samplemmt" table does not exist, create it  
            cursor.execute('''CREATE TABLE IF NOT EXISTS wolseemmt.samplemmt(
                id_no serial PRIMARY KEY,
                material_type TEXT,
                pipe_shape TEXT,
                defect_type TEXT,
                datafile_name TEXT                             
                );
            ''')  
            columns = ['id_no', 'material_type', 'pipe_shape', 'defect_type', 'datafile_name']
            df = pd.read_excel('.\dbinitial\samplelist.xlsx')
            # Convert int64 columns to int
            int_cols = df.select_dtypes(include=['int32', 'int64']).columns
            df[int_cols] = df[int_cols].astype(object) ######这里要改成object，否则会影响原始定义类型!!!!!
            # Replace NaN values with None
            excel_data = df.where(pd.notnull(df), None)
            # Convert DataFrame to a list of tuples
            data=[tuple(row) for row in excel_data.to_records(index=False)]
            with self.conn.cursor() as cursor:
                insert_query = f"INSERT INTO wolseeMMT.samplemmt ({', '.join(columns)}) VALUES ({', '.join(['%s']*len(columns))}) ON CONFLICT DO NOTHING;"
                cursor.executemany(insert_query, data)
        cursor.close()

        '''
        cur = self.conn.cursor()
        self.conn.autocommit = True
        testtree = ET.parse('D:\Projects//business\wolseeMMT-recovery\dbinitial\Ori0063.xml')
        testroot = testtree.getroot()
        # Iterate over the <chart> elements and insert their XML data into the PostgreSQL table
        #cur.execute("SELECT EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema = 'wolseemmt' AND table_name = 'samplemmt');")
        xml_data = ET.tostring(testroot, encoding='unicode')
        cur.execute("UPDATE wolseemmt.samplemmt SET xml_data = %s WHERE id_no = %s", (xml_data, 1))
        #for chart in testroot.findall('chart'):
        #    xml_data = ET.tostring(chart, encoding='unicode')
        #    #cur.execute("INSERT INTO wolseemmt.samplemmt (xml_data) VALUES (%s)", (xml_data,))
        #    cur.execute("UPDATE wolseemmt.samplemmt SET xml_data = %s WHERE id_no = %s", (xml_data, 1))
        # Commit the changes and close the database connection
        #self.conn.commit()
        #cur.close()
        '''

    def userdbcreat(self):
        cursor = self.conn.cursor()
        self.conn.autocommit = True            
        cursor.execute('''CREATE TABLE IF NOT EXISTS wolseemmt.usermmtdata(
            id_no serial PRIMARY KEY,
            input_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
            test_location TEXT,                     
            material_type TEXT,
            pipe_shape TEXT,
            defect_type TEXT,
            datafile_name TEXT, 
            remark TEXT                 
            );
        ''')    
        cursor.close()

    def insertuserdata():
        pass    

class DataTableShowWindow(QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.setWindowTitle("数据库样本数据显示")
        self.setLayout(QVBoxLayout())
        self.resize(700, 900)
        
        self.table_view = QTableWidget(self)
        # Open the Excel file and read the data
        wb = openpyxl.load_workbook('./dbinitial/samplelist.xlsx')
        ws = wb.active
        # Convert the generator object to a list
        rows = list(ws.iter_rows(values_only=True))
        # Set the table widget
        self.table_view.setColumnCount(ws.max_column+1)
        self.table_view.setRowCount(ws.max_row)
        self.table_view.setEditTriggers(QAbstractItemView.NoEditTriggers)  # Set the table widget to read-only
        # Header labels mapping
        header_mapping = {
            'id_no': '数据编号',
            'material_type': '管道材质',
            'pipe_shape': '管道形态',
            'defect_type': '缺陷类型',
            'datafile_name': '数据文件名'
        }
        # Replace the header labels with translations
        header_row = next(ws.iter_rows(values_only=True))
        translated_header = [header_mapping.get(label, label) for label in header_row]
        translated_header.append("打开文件")
        self.table_view.setHorizontalHeaderLabels(translated_header)
        # Iterate over the Excel cells and populate the table widget
        for fixrow, row in enumerate(rows):
            if fixrow == 0:
                continue
            row_index = fixrow-1
            for col_index, cell_value in enumerate(row):
                item = QTableWidgetItem(str(cell_value))
                self.table_view.setItem(row_index, col_index, item) 
                # Add a button to each cell in the "datafile_name" column
                if translated_header[col_index] == '数据文件名':
                    button = QPushButton('Open')
                    button.clicked.connect(self.transfer_filename)
                    self.table_view.setCellWidget(row_index, col_index+1, button)
        # Add the table widget to the layout
        self.layout().addWidget(self.table_view)
        
    '''
        self.button = QPushButton('Refresh', self)
        self.button.clicked.connect(self.refresh_table)
        self.layout().addWidget(self.button)
        self.model = QStandardItemModel(self)
        self.table_view.setModel(self.model)
        self.refresh_table()  #button.clicked.connect(self.open_widget(item.text()))
    def refresh_table(self):
        show_conn = DBOperate(int(config.get('Database Connect', 'port')), config.get('Database Connect', 'username'), config.get('Database Connect', 'userpw'))
        ifok = show_conn.connect()
        # Read the data from the "sample" table
        cur = ifok.cursor()
        cur.execute("SELECT * FROM wolseemmt.samplemmt")
        data = cur.fetchall()
        cur.close()
        ifok.close()
        # Clear the model and add the data to it
        self.model.clear()
        #self.model.setHorizontalHeaderLabels(['id_no', 'material_type', 'pipe_shape', 'defect_type', 'datafile_name'])
        self.model.setHorizontalHeaderLabels(['数据编号', '管道材质', '管道形态', '缺陷类型', '数据文件名'])
        for row in data:
            items = [QStandardItem(str(item)) for item in row]
            self.model.appendRow(items)
        # Resize the columns to fit the contents
        self.table_view.resizeColumnsToContents()        
    '''

    # Function to open a new widget when the button is clicked
    def transfer_filename(self):
        button = self.sender()
        row = self.table_view.indexAt(button.pos()).row() #self.indexAt(button.pos()).row()
        name_item = self.table_view.item(row, 4) # 4 is the column index of the "datafile_name" column
        name = name_item.text()
        print(f"File Name is: {name}")
        mainwidget.tabWidget_Show(file_path='./dbinitial/'+name)
        
         

class MainWindow(QMainWindow, Ui_MainWindow):
    def __init__(self, parent=None):
        super(MainWindow,self).__init__()
        self.setupUi(self)
        self.statusbar.showMessage('Developed by Fungear Digital Technology Co., Ltd.')

        #self.clearLayout(self.drawingTab.layout())

        # helpmenu Connect Action
        self.actionAbout.triggered.connect(self.on_actionAbout)
        self.actionDocument.triggered.connect(self.on_actionDocument)

        # DBMmenu Connect Action
        self.actionInitialDB.triggered.connect(self.on_actionDBInitial) 

        # Editmenu Connect Action
        #self.actionUndo.triggered.connect(self.text_edit.undo)
        #self.actionRedo.triggered.connect(self.text_edit.redo)   
        #self.actionZoomOut.triggered.connect(self.text_edit.zoomOut)
        #self.actionZoomIn.triggered.connect(self.text_edit.zoomIn)
        #self.actionClearForm.triggered.connect(self.text_edit.clear)    

        # Filemenu Connect Action
        self.actionOpen_File.triggered.connect(self.on_actionOpen_File) 
        self.actionOpen_DB_File.triggered.connect(self.on_actionOpen_DB_File) 
        self.actionSave_as_File.triggered.connect(self.on_actionSave_as_File)
        self.actionExit.triggered.connect(QApplication.instance().quit) 

        # pca
        self.actionPrincipalComponent_A.triggered.connect(self.on_actionPCA)

        # drawingTab and sheetTab widget Connect 
        #filename = OpenFileForm().on_openfile_Button_clicked()
        #print(filename)
        #self.tabWidget_Show('C:/Users/nolan/Desktop/7.xml')
        
    # def on_actionAbout
    def on_actionAbout(self):
        aboutform.show()
    
    def on_actionDocument(self):
        file_path = "操作手册.pdf"
        QDesktopServices.openUrl(QUrl.fromLocalFile(file_path))   

    def on_actionDBInitial(self):
        dbinitialform.show()   

    def on_actionOpen_File(self):
        openfileform.show()

    def on_actionOpen_DB_File(self):
        self.sample = DataTableShowWindow()
        self.sample.show()
    
    def on_actionSave_as_File(self):
        config = configparser.ConfigParser()
        config.read('.\datauser\currentstore.ini', encoding='utf-8')
        file_path = config.get('Current File Store Info','filepath')
        if file_path == 'none':
            print("当前没有打开的用户文件")
        else:
            print(file_path)
            savedialog = QFileDialog()
            savedialog.setAcceptMode(QFileDialog.AcceptSave)
            savedialog.setNameFilter("XML Files (*.xml);;Excel Files (*.xlsx *.xls)")
            savedialog.setDefaultSuffix("xml")
            if savedialog.exec():
                saveasfile_path = savedialog.selectedFiles()[0]
                with open(file_path, 'r', encoding='latin-1') as f:
                    file_contents = f.read()
                with open(saveasfile_path, 'w', encoding='utf-8') as f:
                    f.write(file_contents)

    def on_actionPCA(self):
        '''
        file_dialog = QFileDialog(self)
        file_dialog.setNameFilter("All Files (*.*)")
        if file_dialog.exec() == QFileDialog.Accepted:
            file_path = file_dialog.selectedFiles()[0]
        self.dataReader = ExcelDataReader(file_path)
        '''
        w = PCAWidget(self.dataReader)
        w.exec()
        
    def tabWidget_Show(self, file_path):
        # load data
        if file_path.endswith('.xml'):
            self.dataReader = XMLDataReader(file_path)
        if file_path.endswith('.xlsx') or file_path.endswith('.xls'):
            self.dataReader = ExcelDataReader(file_path)

        # Remove existing layout
        drawinglayout = self.drawingTab.layout()
        if drawinglayout:
            item = drawinglayout.takeAt(0)
            del item
        else:
            drawinglayout = QVBoxLayout(self.drawingTab)
            self.drawingTab.setLayout(drawinglayout)
        drawinglayout.addWidget(tabWidgetDrawingShow(self.dataReader))

        # table widget
        tablelayout = self.sheetTab.layout()
        if tablelayout:
            item = tablelayout.takeAt(0)
            del item
        else:
            tablelayout = QVBoxLayout(self.sheetTab)
            self.sheetTab.setLayout(tablelayout)
        tablelayout.addWidget(tabWidgetTableShow(self.dataReader))

    #重载内置的closeEvent方法，实现关闭窗口时的操作
    def closeEvent(self, event):
        config = configparser.ConfigParser()
        with open('.\datauser\currentstore.ini', encoding='utf-8') as config_file:
            config.read(config_file) 
        config['Current File Store Info'] = {}
        config['Current File Store Info']['filepath'] = 'none'
        config_file_path = os.path.join(os.getcwd(), '.\datauser\currentstore.ini')
        with open(config_file_path, 'w', encoding='utf-8') as config_file:
            config.write(config_file)
        event.accept()    


if __name__ == "__main__":
    
    app = QApplication(sys.argv)

    aboutform = AboutForm()
    dbinitialform = DBinitialForm()
    openfileform = OpenFileForm()
    
    mainwidget = MainWindow() #定义在主程序内的变量为全局变量，该文件任何其他地方都可以引用
    mainwidget.show()
    

    dbfile_path = ".\dbinitial\dbconfig.ini"
    if not Path(dbfile_path).exists():
        dbinitialform.show()
    else:
        config = configparser.ConfigParser()
        config.read(dbfile_path) 
        dbconnect = DBOperate(int(config.get('Database Connect', 'port')), config.get('Database Connect', 'username'), config.get('Database Connect', 'userpw'))
        isok = dbconnect.connect()
        if isok == "Fail": 
            dbinitialform.show()
            print("连接数据库失败，重新配置config文件")
        if  isok == "OK":
            mainwidget.actionInitialDB.setEnabled(False)
            print("这是主程序连接库成功")
            dbconnect.sampleinitial()
            dbconnect.userdbcreat()

    
    sys.exit(app.exec())


    